/*sensitive_col -- Column identified as possible sensistive data based on col keyword name and mapping confidence
keyword_exclusion -- Exclusions
keyword -- keyword  wildcard search characters
data_category_lku -- sensitive data element categories
mapping_confidence_lku
-- mapping confidence 
A = automated
V = Validated
G = Global
S = SME
Z = Zero Row Counts
*/

USE [piiDW]
GO
/****** Object:  Table [dbo].[mapping_confidence_lku]    Script Date: 09/24/2009 16:51:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mapping_confidence_lku](
	[mapping_confidence_code] [char](1) NOT NULL,
	[mapping_confidence_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_mapping_confidence_lku] PRIMARY KEY CLUSTERED 
(
	[mapping_confidence_code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[data_cat_lku]    Script Date: 09/24/2009 16:51:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[data_cat_lku](
	[data_cat_id] [int] IDENTITY(1,1) NOT NULL,
	[data_cat_name] [varchar](100) NULL,
	[data_cat_short_name] [varchar](20) NULL,
	[desc_text] [varchar](100) NULL,
 CONSTRAINT [PK_data_cat_lku] PRIMARY KEY CLUSTERED 
(
	[data_cat_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_data_cat_lku] UNIQUE NONCLUSTERED 
(
	[data_cat_name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[keyword]    Script Date: 09/24/2009 16:51:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[keyword](
	[keyword_id] [int] IDENTITY(1,1) NOT NULL,
	[keyword_text] [varchar](255) NOT NULL,
	[data_cat_id] [int] NULL,
 CONSTRAINT [PK_keyword] PRIMARY KEY CLUSTERED 
(
	[keyword_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[sensitive_col]    Script Date: 09/24/2009 16:51:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sensitive_col](
	[sensitive_col_id] [int] IDENTITY(1,1) NOT NULL,
	[env_code] [char](8) NULL,
	[instance_name] [varchar](100) NULL,
	[data_base_name] [varchar](100) NULL,
	[table_name] [varchar](100) NULL,
	[col_name] [varchar](150) NULL,
	[keyword_id] [int] NULL,
	[mapping_confidence_code] [char](1) NOT NULL,
	[pk_flag] [char](1) NULL,
	[data_type] [varchar](200) NULL,
	[data_size] [int] NULL,
 CONSTRAINT [PK_sensitive_col] PRIMARY KEY NONCLUSTERED 
(
	[sensitive_col_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [AK_sensitive_col] UNIQUE CLUSTERED 
(
	[instance_name] ASC,
	[data_base_name] ASC,
	[table_name] ASC,
	[col_name] ASC,
	[data_type] ASC,
	[data_size] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[keyword_exclusion]    Script Date: 09/24/2009 16:51:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[keyword_exclusion](
	[keyword_exclusion_id] [int] IDENTITY(1,1) NOT NULL,
	[sensitive_col_id] [int] NULL,
	[env_code] [char](1) NULL,
	[instance_name] [varchar](100) NULL,
	[data_base_name] [varchar](100) NULL,
	[table_name] [varchar](100) NULL,
	[col_name] [varchar](150) NULL,
	[key_word_id] [int] NULL,
 CONSTRAINT [PK_keyword_exclusion] PRIMARY KEY CLUSTERED 
(
	[keyword_exclusion_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Default [DF_sensitive_col_mapping_confidence_code]    Script Date: 09/24/2009 16:51:57 ******/
ALTER TABLE [dbo].[sensitive_col] ADD  CONSTRAINT [DF_sensitive_col_mapping_confidence_code]  DEFAULT ('A') FOR [mapping_confidence_code]
GO
/****** Object:  ForeignKey [FK_keyword_data_cat_lku]    Script Date: 09/24/2009 16:51:57 ******/
ALTER TABLE [dbo].[keyword]  WITH CHECK ADD  CONSTRAINT [FK_keyword_data_cat_lku] FOREIGN KEY([data_cat_id])
REFERENCES [dbo].[data_cat_lku] ([data_cat_id])
GO
ALTER TABLE [dbo].[keyword] CHECK CONSTRAINT [FK_keyword_data_cat_lku]
GO
/****** Object:  ForeignKey [FK_keyword_exclusion_sensitive_col]    Script Date: 09/24/2009 16:51:57 ******/
ALTER TABLE [dbo].[keyword_exclusion]  WITH CHECK ADD  CONSTRAINT [FK_keyword_exclusion_sensitive_col] FOREIGN KEY([sensitive_col_id])
REFERENCES [dbo].[sensitive_col] ([sensitive_col_id])
GO
ALTER TABLE [dbo].[keyword_exclusion] CHECK CONSTRAINT [FK_keyword_exclusion_sensitive_col]
GO
/****** Object:  ForeignKey [FK_sensitive_col_keyword]    Script Date: 09/24/2009 16:51:57 ******/
ALTER TABLE [dbo].[sensitive_col]  WITH CHECK ADD  CONSTRAINT [FK_sensitive_col_keyword] FOREIGN KEY([keyword_id])
REFERENCES [dbo].[keyword] ([keyword_id])
GO
ALTER TABLE [dbo].[sensitive_col] CHECK CONSTRAINT [FK_sensitive_col_keyword]
GO
/****** Object:  ForeignKey [FK_sensitive_col_mapping_confidence_lku]    Script Date: 09/24/2009 16:51:57 ******/
ALTER TABLE [dbo].[sensitive_col]  WITH CHECK ADD  CONSTRAINT [FK_sensitive_col_mapping_confidence_lku] FOREIGN KEY([mapping_confidence_code])
REFERENCES [dbo].[mapping_confidence_lku] ([mapping_confidence_code])
GO
ALTER TABLE [dbo].[sensitive_col] CHECK CONSTRAINT [FK_sensitive_col_mapping_confidence_lku]
GO

